library(RSQLite)
library(DBI)
library(dplyr)
library(knitr)
path <- "../SQL"
fname <- file.path(path, "disco.db")Desafio 6
1. Configuração Inicial
2. Conexão com o Banco de Dados
conn <- dbConnect(RSQLite::SQLite(), fname)
class(conn)[1] "SQLiteConnection"
attr(,"package")
[1] "RSQLite"3. Listando as tabelas existentes no banco de dados
tabelas <- dbListTables(conn)
print(tabelas) [1] "albums" "artists" "customers" "employees"
[5] "genres" "invoice_items" "invoices" "media_types"
[9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
[13] "tracks" 4. Identificando Colunas da Tabela customers
colunas_customers <- dbListFields(conn, "customers")
print(colunas_customers) [1] "CustomerId" "FirstName" "LastName" "Company" "Address"
[6] "City" "State" "Country" "PostalCode" "Phone"
[11] "Fax" "Email" "SupportRepId"5. Contagem Total de Clientes
query_total_clientes <- "
SELECT COUNT(CustomerId) AS total_de_clientes
FROM customers;
"
resultado_total_clientes <- dbGetQuery(conn, query_total_clientes)
print(resultado_total_clientes) total_de_clientes
1 596. Contagem de Países Distintos
query_paises_distintos <- "
SELECT COUNT(DISTINCT Country) AS numero_de_paises_distintos
FROM customers;
"
resultado_paises_distintos <- dbGetQuery(conn, query_paises_distintos)
print(resultado_paises_distintos) numero_de_paises_distintos
1 247. Contagem de Clientes por País
query_clientes_por_pais <- "
SELECT
Country,
COUNT(CustomerId) AS contagem
FROM customers
GROUP BY Country
ORDER BY contagem DESC;
"
resultado_clientes_por_pais <- dbGetQuery(conn, query_clientes_por_pais)
print(resultado_clientes_por_pais) Country contagem
1 USA 13
2 Canada 8
3 France 5
4 Brazil 5
5 Germany 4
6 United Kingdom 3
7 Portugal 2
8 India 2
9 Czech Republic 2
10 Sweden 1
11 Spain 1
12 Poland 1
13 Norway 1
14 Netherlands 1
15 Italy 1
16 Ireland 1
17 Hungary 1
18 Finland 1
19 Denmark 1
20 Chile 1
21 Belgium 1
22 Austria 1
23 Australia 1
24 Argentina 18. Os 5 Países com Mais Clientes
query_top5_paises <- "
SELECT
Country,
COUNT(CustomerId) AS contagem
FROM customers
GROUP BY Country
ORDER BY contagem DESC
LIMIT 5;
"
resultado_top5_paises <- dbGetQuery(conn, query_top5_paises)
print(resultado_top5_paises) Country contagem
1 USA 13
2 Canada 8
3 France 5
4 Brazil 5
5 Germany 49. Países com Nomes de 6 Letras
query_paises_length_6 <- "
SELECT DISTINCT Country
FROM customers
WHERE LENGTH(Country) = 6;
"
resultado_paises_length_6 <- dbGetQuery(conn, query_paises_length_6)
print(resultado_paises_length_6) Country
1 Brazil
2 Canada
3 Norway
4 France
5 Poland
6 Sweden10. Músicas Compradas por Clientes Brasileiros
query_musicas_brasil <- "
SELECT
T.Name AS Musica,
C.FirstName || ' ' || C.LastName AS Cliente
FROM customers AS C
JOIN invoices AS I ON C.CustomerId = I.CustomerId
JOIN invoice_items AS II ON I.InvoiceId = II.InvoiceId
JOIN tracks AS T ON II.TrackId = T.TrackId
WHERE C.Country = 'Brazil'
ORDER BY Cliente, Musica;
"
resultado_musicas_brasil <- dbGetQuery(conn, query_musicas_brasil)
print(resultado_musicas_brasil) Musica
1 2 Minutes To Midnight
2 Animal
3 Be Mine
4 Bora-Bora
5 Bossa
6 Cristina Nº 2
7 Cry For Love
8 Damage Inc.
9 Eu Amo Você
10 Get Down, Make Love
11 Get Up
12 Green Disease
13 Grito De Alerta
14 I Feel Good (I Got You) - Sossego
15 Into The Light
16 Is This Love (Live)
17 Leash
18 Leper Messiah
19 Lindo Lago Do Amor
20 Losfer Words
21 Nega Do Cabelo Duro
22 Nosso Adeus
23 Não Vou Ficar
24 O Descobridor Dos Sete Mares
25 O Leaozinho
26 O Que Me Importa
27 Ponto De Interrogação
28 Redundant
29 Selvagem
30 Seven Seas Of Rhye
31 Sincero Breu
32 The Great Gig In The Sky
33 Top Top
34 Untitled
35 Voce Nao Entende Nada - Cotidiano
36 Wanted Dread And Alive
37 Why Go
38 You're My Best Friend
39 1/2 Full
40 Aces High
41 Admirável Gado Novo
42 All I Want Is You
43 Any Colour You Like
44 Babyface
45 Bad
46 Big Wave
47 Binky The Doormat
48 Bittersweet Me
49 Black
50 Child In Time
51 Cropduster
52 D'Yer Mak'er
53 Dissident
54 Don't Look Back
55 Down Under
56 Drifter
57 Electrolite
58 Knocking At Your Back Door
59 Like A Song...
60 Maybe I'm A Leo
61 Mis Penas Lloraba Yo (Ao Vivo) Soy Gitano (Tangos)
62 Most High
63 No No No
64 No Quarter
65 Oceans
66 Pick Myself Up
67 Plot 180
68 Praise
69 Red Light
70 Sanctuary
71 So Fast, So Numb
72 Sweetest Thing
73 The Unwritten Law
74 Untitled
75 Vavoom : Ted The Mechanic
76 Your Blue Room
77 A Cura
78 A Menina Dança
79 Abraham, Martin And John
80 Aloha
81 Bad Seed
82 Black Light Syndrome
83 Casa
84 Cérebro Eletrônico
85 Duelists
86 Dust N' Bones
87 Everything I Need
88 Fast As a Shark
89 Girl From A Pawnshop
90 Go Down
91 Heart Of Lothian: Wide Boy / Curtain Call
92 Hell Ain't A Bad Place To Be
93 Help Yourself
94 I Belong To You
95 Lay Down Sally
96 Live and Let Die
97 Midnight From The Inside Out
98 Music for the Funeral of Queen Mary: VI. "Thou Knowest, Lord, the Secrets of Our Hearts"
99 Pais E Filhos
100 Powerslave
101 Rock And Roll Is Dead
102 Sereia
103 Será
104 Slither
105 Snowballed
106 Sometimes Salvation
107 Soul Singing
108 String Quartet No. 12 in C Minor, D. 703 "Quartettsatz": II. Andante - Allegro assai
109 Suite No. 3 in D, BWV 1068: III. Gavotte I & II
110 Symphony No. 2, Op. 16 - "The Four Temperaments": II. Allegro Comodo e Flemmatico
111 The Memory Remains
112 The Unforgiven II
113 Title Song
114 You've Been A Long Time Coming
115 A Cor Do Sol
116 All Along The Watchtower
117 Back off Bitch
118 Calling Dr. Love
119 Carolina Hard-Core Ecstasy
120 Cinema Mudo
121 Cold Gin
122 Coma
123 Communication Breakdown(2)
124 Don't Cry (Original)
125 Doutor
126 Experiment In Terra
127 Fantasia On Greensleeves
128 Flying High Again
129 Garden of Eden
130 Interlude Zumbi
131 Ipiranga 2001
132 Karelia Suite, Op.11: 2. Ballade (Tempo Di Menuetto)
133 Linha Do Equador
134 Lixo Do Mangue
135 Mama, I'm Coming Home
136 Mensagen De Amor (2000)
137 Meu Erro
138 Paranoid
139 Podes Crer
140 Reggae Tchan
141 Rios Pontes & Overdrives
142 Rocket Queen
143 Saber Amar
144 Será Que Vai Chover?
145 Shout It Out Loud
146 Strutter
147 Take the Celestra
148 Think About You
149 TriboTchan
150 Vamo Batê Lata
151 X-9 2001
152 Água de Beber
153 Amor De Muito
154 Aos Leões
155 Banditismo Por Uma Questa
156 Be Good Johnny
157 Burden In My Hand
158 Caso Você Queira Saber
159 Dance
160 Demorou!
161 Down by the Sea
162 Eye
163 Flower
164 Fool In The Rain
165 Home Sweet Home
166 In Bloom
167 In The Evening
168 Intro
169 It's a Mistake
170 Jesus Christ Pose
171 Maracatu De Tiro Certeiro
172 Mateus Enter
173 Meu Caro Amigo
174 Morena De Angola
175 Mundaréu
176 Nossa Gente (Avisa Là)
177 On A Plain
178 Refavela (Live)
179 Revolta Olodum
180 Right Next Door to Hell
181 Samba Do Lado
182 Saudade Dos Aviões Da Panair (Conversando No Bar)
183 Scentless Apprentice
184 Shakes and Ladders
185 Stand Inside Your Love
186 Stir It Up (Live)
187 The Day I Tried To Live
188 Um Satélite Na Cabeça
189 Vai Passar
190 Vai Valer
Cliente
1 Alexandre Rocha
2 Alexandre Rocha
3 Alexandre Rocha
4 Alexandre Rocha
5 Alexandre Rocha
6 Alexandre Rocha
7 Alexandre Rocha
8 Alexandre Rocha
9 Alexandre Rocha
10 Alexandre Rocha
11 Alexandre Rocha
12 Alexandre Rocha
13 Alexandre Rocha
14 Alexandre Rocha
15 Alexandre Rocha
16 Alexandre Rocha
17 Alexandre Rocha
18 Alexandre Rocha
19 Alexandre Rocha
20 Alexandre Rocha
21 Alexandre Rocha
22 Alexandre Rocha
23 Alexandre Rocha
24 Alexandre Rocha
25 Alexandre Rocha
26 Alexandre Rocha
27 Alexandre Rocha
28 Alexandre Rocha
29 Alexandre Rocha
30 Alexandre Rocha
31 Alexandre Rocha
32 Alexandre Rocha
33 Alexandre Rocha
34 Alexandre Rocha
35 Alexandre Rocha
36 Alexandre Rocha
37 Alexandre Rocha
38 Alexandre Rocha
39 Eduardo Martins
40 Eduardo Martins
41 Eduardo Martins
42 Eduardo Martins
43 Eduardo Martins
44 Eduardo Martins
45 Eduardo Martins
46 Eduardo Martins
47 Eduardo Martins
48 Eduardo Martins
49 Eduardo Martins
50 Eduardo Martins
51 Eduardo Martins
52 Eduardo Martins
53 Eduardo Martins
54 Eduardo Martins
55 Eduardo Martins
56 Eduardo Martins
57 Eduardo Martins
58 Eduardo Martins
59 Eduardo Martins
60 Eduardo Martins
61 Eduardo Martins
62 Eduardo Martins
63 Eduardo Martins
64 Eduardo Martins
65 Eduardo Martins
66 Eduardo Martins
67 Eduardo Martins
68 Eduardo Martins
69 Eduardo Martins
70 Eduardo Martins
71 Eduardo Martins
72 Eduardo Martins
73 Eduardo Martins
74 Eduardo Martins
75 Eduardo Martins
76 Eduardo Martins
77 Fernanda Ramos
78 Fernanda Ramos
79 Fernanda Ramos
80 Fernanda Ramos
81 Fernanda Ramos
82 Fernanda Ramos
83 Fernanda Ramos
84 Fernanda Ramos
85 Fernanda Ramos
86 Fernanda Ramos
87 Fernanda Ramos
88 Fernanda Ramos
89 Fernanda Ramos
90 Fernanda Ramos
91 Fernanda Ramos
92 Fernanda Ramos
93 Fernanda Ramos
94 Fernanda Ramos
95 Fernanda Ramos
96 Fernanda Ramos
97 Fernanda Ramos
98 Fernanda Ramos
99 Fernanda Ramos
100 Fernanda Ramos
101 Fernanda Ramos
102 Fernanda Ramos
103 Fernanda Ramos
104 Fernanda Ramos
105 Fernanda Ramos
106 Fernanda Ramos
107 Fernanda Ramos
108 Fernanda Ramos
109 Fernanda Ramos
110 Fernanda Ramos
111 Fernanda Ramos
112 Fernanda Ramos
113 Fernanda Ramos
114 Fernanda Ramos
115 Luís Gonçalves
116 Luís Gonçalves
117 Luís Gonçalves
118 Luís Gonçalves
119 Luís Gonçalves
120 Luís Gonçalves
121 Luís Gonçalves
122 Luís Gonçalves
123 Luís Gonçalves
124 Luís Gonçalves
125 Luís Gonçalves
126 Luís Gonçalves
127 Luís Gonçalves
128 Luís Gonçalves
129 Luís Gonçalves
130 Luís Gonçalves
131 Luís Gonçalves
132 Luís Gonçalves
133 Luís Gonçalves
134 Luís Gonçalves
135 Luís Gonçalves
136 Luís Gonçalves
137 Luís Gonçalves
138 Luís Gonçalves
139 Luís Gonçalves
140 Luís Gonçalves
141 Luís Gonçalves
142 Luís Gonçalves
143 Luís Gonçalves
144 Luís Gonçalves
145 Luís Gonçalves
146 Luís Gonçalves
147 Luís Gonçalves
148 Luís Gonçalves
149 Luís Gonçalves
150 Luís Gonçalves
151 Luís Gonçalves
152 Luís Gonçalves
153 Roberto Almeida
154 Roberto Almeida
155 Roberto Almeida
156 Roberto Almeida
157 Roberto Almeida
158 Roberto Almeida
159 Roberto Almeida
160 Roberto Almeida
161 Roberto Almeida
162 Roberto Almeida
163 Roberto Almeida
164 Roberto Almeida
165 Roberto Almeida
166 Roberto Almeida
167 Roberto Almeida
168 Roberto Almeida
169 Roberto Almeida
170 Roberto Almeida
171 Roberto Almeida
172 Roberto Almeida
173 Roberto Almeida
174 Roberto Almeida
175 Roberto Almeida
176 Roberto Almeida
177 Roberto Almeida
178 Roberto Almeida
179 Roberto Almeida
180 Roberto Almeida
181 Roberto Almeida
182 Roberto Almeida
183 Roberto Almeida
184 Roberto Almeida
185 Roberto Almeida
186 Roberto Almeida
187 Roberto Almeida
188 Roberto Almeida
189 Roberto Almeida
190 Roberto Almeida11. Qual o álbum mais tocado por país?
query_album_por_pais <- "
WITH VendasAlbumPorPais AS (
SELECT
C.Country,
AL.Title AS Album,
AR.Name AS Artista,
COUNT(II.InvoiceLineId) AS Vendas,
ROW_NUMBER() OVER(PARTITION BY C.Country ORDER BY COUNT(II.InvoiceLineId) DESC) as Rank
FROM customers AS C
JOIN invoices AS I ON C.CustomerId = I.CustomerId
JOIN invoice_items AS II ON I.InvoiceId = II.InvoiceId
JOIN tracks AS T ON II.TrackId = T.TrackId
JOIN albums AS AL ON T.AlbumId = AL.AlbumId
JOIN artists AS AR ON AL.ArtistId = AR.ArtistId
GROUP BY C.Country, AL.Title
)
SELECT
Country,
Album,
Artista,
Vendas
FROM VendasAlbumPorPais
WHERE Rank = 1
ORDER BY Country;
"
resultado_album_por_pais <- dbGetQuery(conn, query_album_por_pais)
print(resultado_album_por_pais) Country Album
1 Argentina Acústico
2 Australia The X Factor
3 Austria My Generation - The Very Best Of The Who
4 Belgium Rotten Apples: Greatest Hits
5 Brazil Use Your Illusion I
6 Canada Arquivo II
7 Chile My Way: The Best Of Frank Sinatra [Disc 1]
8 Czech Republic Prenda Minha
9 Denmark Mezmerize
10 Finland Greatest Kiss
11 France Minha Historia
12 Germany Unplugged
13 Hungary Pop
14 India Up An' Atom
15 Ireland Lost, Season 2
16 Italy Acústico
17 Netherlands In Step
18 Norway For Those About To Rock We Salute You
19 Poland Rattle And Hum
20 Portugal Raul Seixas
21 Spain My Generation - The Very Best Of The Who
22 Sweden Volume Dois
23 USA The Office, Season 3
24 United Kingdom No Prayer For The Dying
Artista Vendas
1 Titãs 4
2 Iron Maiden 4
3 The Who 4
4 Smashing Pumpkins 4
5 Guns N' Roses 7
6 Os Paralamas Do Sucesso 9
7 Frank Sinatra 3
8 Caetano Veloso 5
9 System Of A Down 4
10 Kiss 5
11 Chico Buarque 7
12 Eric Clapton 5
13 U2 4
14 Gene Krupa 6
15 Lost 7
16 Titãs 4
17 Stevie Ray Vaughan & Double Trouble 4
18 AC/DC 4
19 U2 4
20 Raul Seixas 4
21 The Who 5
22 Titãs 4
23 The Office 14
24 Iron Maiden 512. Qual o artista mais tocado por país?
query_artista_por_pais <- "
WITH VendasArtistaPorPais AS (
SELECT
C.Country,
AR.Name AS Artista,
COUNT(II.InvoiceLineId) AS Vendas,
ROW_NUMBER() OVER(PARTITION BY C.Country ORDER BY COUNT(II.InvoiceLineId) DESC) as Rank
FROM customers AS C
JOIN invoices AS I ON C.CustomerId = I.CustomerId
JOIN invoice_items AS II ON I.InvoiceId = II.InvoiceId
JOIN tracks AS T ON II.TrackId = T.TrackId
JOIN albums AS AL ON T.AlbumId = AL.AlbumId
JOIN artists AS AR ON AL.ArtistId = AR.ArtistId
GROUP BY C.Country, AR.Name
)
SELECT
Country,
Artista,
Vendas
FROM VendasArtistaPorPais
WHERE Rank = 1
ORDER BY Country;
"
resultado_artista_por_pais <- dbGetQuery(conn, query_artista_por_pais)
print(resultado_artista_por_pais) Country Artista Vendas
1 Argentina Metallica 6
2 Australia Iron Maiden 18
3 Austria U2 9
4 Belgium Faith No More 9
5 Brazil Pearl Jam 11
6 Canada Os Paralamas Do Sucesso 16
7 Chile Led Zeppelin 6
8 Czech Republic U2 9
9 Denmark Creedence Clearwater Revival 7
10 Finland Van Halen 6
11 France Creedence Clearwater Revival 11
12 Germany Iron Maiden 14
13 Hungary The Office 5
14 India Iron Maiden 8
15 Ireland Lost 8
16 Italy The Rolling Stones 5
17 Netherlands Red Hot Chili Peppers 6
18 Norway Led Zeppelin 9
19 Poland U2 6
20 Portugal Iron Maiden 16
21 Spain Pearl Jam 6
22 Sweden Titãs 5
23 USA Iron Maiden 34
24 United Kingdom Metallica 913. Desconexão
dbDisconnect(conn)
print("Conexão com o banco de dados foi fechada com sucesso.")[1] "Conexão com o banco de dados foi fechada com sucesso."